#!/usr/bin/python
#-*- coding: UTF-8 -*-

from django.db import models
from django.utils.translation import ugettext_lazy as _
from icq.traffic_statistics.db import *

class Department(models.Model):
    id = models.AutoField(primary_key=True)
    department_name = models.CharField(max_length=50, verbose_name='部门名称')

    def __unicode__(self):
        return self.department_name

    class Meta:
        verbose_name_plural = _(u'事业部')


class AbstractChannel(models.Model):
    parent = models.ForeignKey('self', related_name='child', verbose_name='上级频道')
    class Meta:
        abstract = True


class Channel(AbstractChannel):
    cid = models.IntegerField(primary_key=True, verbose_name='频道ID')
    channel_name = models.CharField(max_length=50, verbose_name='频道名称')
    owner_department = models.ForeignKey(Department, verbose_name='所属部门')

    def __unicode__(self):
        return self.channel_name

    class Meta:
        verbose_name_plural = _(u'频道&论坛&专题')


class Group(models.Model):
    cyid = models.IntegerField(primary_key=True, verbose_name='圈层ID')
    group_name = models.CharField(max_length=100, verbose_name='圈层名称')
    owner_department = models.ForeignKey(Department, verbose_name='所属部门')

    def __unicode__(self):
        return self.group_name

    class Meta:
        verbose_name_plural = _(u'圈层')


class Total(models.Model):
    id = models.AutoField(primary_key=True)
    department_id = models.IntegerField(verbose_name='部门ID')
    cid = models.IntegerField(verbose_name='频道ID')
    cyid = models.IntegerField(verbose_name='圈层ID')
    aid = models.IntegerField(verbose_name='帖子ID')
    pv = models.IntegerField()
    uv = models.IntegerField()
    ip = models.IntegerField()
    dt = models.DateField(verbose_name='日期')

    def __unicode__(self):
        return self.id

    class Meta:
        verbose_name_plural = _(u'数据汇总')

    def www_total(self, did, start, end):
        """
            根据获取指定日期的全站数据
            did 部门ID
            start 开始日期 格式yyyy-mm-dd
            end   结束日期 格式yyyy-mm-dd
        """
        total = Total.objects.filter(department_id=did).filter(dt__gte=start, dt__lte=end).order_by('dt')
        return total

    def channel_total(self, start,end):
        sql = """
        SELECT sum(a.pv),sum(a.uv),sum(a.ip),c.channel_name
        FROM traffic_statistics_total AS a
        LEFT JOIN traffic_statistics_channel AS c ON a.cid = c.cid
        WHERE c.parent_id = -10000 AND a.cid <> -10000 AND a.dt BETWEEN '%s' AND '%s'
		GROUP BY channel_name
        ORDER BY a.pv DESC
        """
        db = IcqDB()
        data = db.query(sql, (start,end))
        return data

    def group_total(self, start,end):
        sql = """
        SELECT SUM(a.pv), sum(a.uv), sum(a.ip) ,b.group_name
        FROM traffic_statistics_total AS a
        LEFT JOIN traffic_statistics_group AS b ON b.cyid = a.cyid
        WHERE a.cyid IS NOT NULL AND a.cyid <> -9999 AND a.dt BETWEEN '%s' AND '%s'
	    GROUP BY group_name
        ORDER BY pv DESC
        """
        db = IcqDB()
        data = db.query(sql, (start,end))
        return data

    def pv_top100(self, start,end):
        sql = """
        SELECT
        c.department_name,b.channel_name,Count(a.id) AS pv,a.url
        FROM traffic_statistics_log_201106 AS a
        LEFT JOIN traffic_statistics_channel AS b ON a.cid_id = b.cid
        LEFT JOIN traffic_statistics_department AS c ON c.id = a.department_id_id
        WHERE a.dt BETWEEN '%s 00:00:00' AND '%s 23:59:59' AND
        a.aid IS NOT null
        GROUP BY a.aid
        ORDER BY pv DESC
        LIMIT 1,100
        """
        db = IcqDB()
        data = db.query(sql, (start,end))
        return data

    def refer_top100(self, start,end):
        sql = """
        SELECT Count(a.id) AS pv, a.refer
        FROM traffic_statistics_log_201106 AS a
        WHERE a.dt BETWEEN '%s 00:00:00' AND '%s 23:59:59'
        GROUP BY a.refer
        ORDER BY pv DESC
        LIMIT 1,100
        """
        db = IcqDB()
        data = db.query(sql, (start,end))
        return data
    
    def department_all_total(self, start,end):
        """
            根据日期获取所有部门汇总数据
            dt 日期
        """
        sql = """
        SELECT sum(a.pv) as pv,sum(a.uv) as uv ,sum(a.ip) as ip,d.department_name AS dname
        FROM traffic_statistics_total AS a
        LEFT JOIN traffic_statistics_group AS b ON a.cyid = b.cyid
        LEFT JOIN traffic_statistics_channel AS c ON a.cid = c.cid
        LEFT JOIN traffic_statistics_department AS d ON a.department_id = d.id
        WHERE dt BETWEEN '%s' AND '%s'
		GROUP BY a.department_id
        LIMIT 2, 6;
        """
        db = IcqDB()
        data = db.query(sql, (start,end))
        return data

    def department_single_total(self, did, start, end):
        """
            根据获取指定日期的事业部数据
            did 部门ID
            start 开始日期 格式yyyy-mm-dd
            end   结束日期 格式yyyy-mm-dd
        """
        sql = """
        SELECT sum(pv),sum(uv),sum(ip),dt
        FROM traffic_statistics_total
        WHERE department_id = %d AND dt BETWEEN '%s' AND '%s'
        GROUP BY dt
        """
        db = IcqDB()
        data = db.query(sql, (did, start, end))
        return data
        #total = Total.objects.annotate(sum('pv'),sum('uv'),sum('ip')).filter(department_id=2).filter(dt__gte=start, dt__lte=end).group_by('dt')
        #total = Total.objects.annotate(pv=sum('pv'),uv=sum('uv'),ip = sum('ip'))
        #return total
